using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class aFilterDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procaFilter_add]";
        private string _strSPUpdateName = "dbo.[procaFilter_update]";
        private string _strSPDeleteName = "dbo.[procaFilter_delete]";
        private string _strSPDeleteAllName = "dbo.[procaFilter_deleteAll]";
        private string _strSPGetName = "dbo.[procaFilter_get]";
        private string _strSPGetAllName = "dbo.[procaFilter_getall]";
        private string _strSPGetPages = "dbo.[procaFilter_getpaged]";
        private string _strSPIsExist = "dbo.[procaFilter_isexist]";
        private string _strTableName = "aFilter";
        #endregion Local Variable

        #region Method
        public aFilterInfo Get_aFilter(
            Int32 TID
        , ref string sErr)
        {
            aFilterInfo objEntr = new aFilterInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("TID", TID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = (aFilterInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            aFilterInfo result = new aFilterInfo();
            result.TID = (dt.Rows[i]["TID"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["TID"]);
            result.SourceContn = (dt.Rows[i]["SourceContn"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["SourceContn"]);
            result.Tcode = (dt.Rows[i]["Tcode"] == DBNull.Value ? "" : (String)dt.Rows[i]["Tcode"]);
            result.isNot = (dt.Rows[i]["isNot"] == DBNull.Value ? "" : (String)dt.Rows[i]["isNot"]);
            result.isOperator = (dt.Rows[i]["isOperator"] == DBNull.Value ? "" : (String)dt.Rows[i]["isOperator"]);
            result.ValueFrom = (dt.Rows[i]["ValueFrom"] == DBNull.Value ? "" : (String)dt.Rows[i]["ValueFrom"]);
            result.ValueTo = (dt.Rows[i]["ValueTo"] == DBNull.Value ? "" : (String)dt.Rows[i]["ValueTo"]);
            result.isAndOr = (dt.Rows[i]["isAndOr"] == DBNull.Value ? "" : (String)dt.Rows[i]["isAndOr"]);
            result.Field1 = (dt.Rows[i]["Field1"] == DBNull.Value ? "" : (String)dt.Rows[i]["Field1"]);
            result.Field2 = (dt.Rows[i]["Field2"] == DBNull.Value ? "" : (String)dt.Rows[i]["Field2"]);
            result.aUser = (dt.Rows[i]["aUser"] == DBNull.Value ? "" : (String)dt.Rows[i]["aUser"]);
            result.aTable = (dt.Rows[i]["aTable"] == DBNull.Value ? "" : (String)dt.Rows[i]["aTable"]);
            result.DB = (dt.Rows[i]["DB"] == DBNull.Value ? "" : (String)dt.Rows[i]["DB"]);

            return result;
        }

        public DataTable GetAll_aFilter(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable Get_aFilterUser(string User, string TableName, string DB, ref string sErr)
        {
            InitConnect();

            InitCommand(@"SELECT     aFilter.TID, aFilter.SourceContn, aFilter.Tcode, aFilter.isNot, aFilter.isOperator, aFilter.ValueFrom, aFilter.ValueTo, aFilter.isAndOr, aFilter.Field1, aFilter.Field2, aFilter.aUser, aFilter.aTable, aFilter.DB, zTableField.BoxType           
                FROM aFilter LEFT JOIN zTableField ON aFilter.Tcode = zTableField.FieldName AND aFilter.aTable = zTableField.TableName
                WHERE (aFilter.aUser = @aUser) 
                    AND (aFilter.aTable = @aTable) 
                    AND (aFilter.DB = @DB) 
                ORDER BY aFilter.TID ", CommandType.Text);

            AddParameter("aUser", User);
            AddParameter("aTable", TableName);
            AddParameter("DB", DB);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_aFilter(aFilterInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter("TID", objEntr.TID);
            AddParameter("SourceContn", objEntr.SourceContn);
            AddParameter("Tcode", objEntr.Tcode);
            AddParameter("isNot", objEntr.isNot);
            AddParameter("isOperator", objEntr.isOperator);
            AddParameter("ValueFrom", objEntr.ValueFrom);
            AddParameter("ValueTo", objEntr.ValueTo);
            AddParameter("isAndOr", objEntr.isAndOr);
            AddParameter("Field1", objEntr.Field1);
            AddParameter("Field2", objEntr.Field2);
            AddParameter("aUser", objEntr.aUser);
            AddParameter("aTable", objEntr.aTable);
            AddParameter("DB", objEntr.DB);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_aFilter(aFilterInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("TID", objEntr.TID);
            AddParameter("SourceContn", objEntr.SourceContn);
            AddParameter("Tcode", objEntr.Tcode);
            AddParameter("isNot", objEntr.isNot);
            AddParameter("isOperator", objEntr.isOperator);
            AddParameter("ValueFrom", objEntr.ValueFrom);
            AddParameter("ValueTo", objEntr.ValueTo);
            AddParameter("isAndOr", objEntr.isAndOr);
            AddParameter("Field1", objEntr.Field1);
            AddParameter("Field2", objEntr.Field2);
            AddParameter("aUser", objEntr.aUser);
            AddParameter("aTable", objEntr.aTable);
            AddParameter("DB", objEntr.DB);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_aFilter(
            Int32 TID
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("TID", TID);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_aFilterAll(
            String aUser, String aTable, String DB
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteAllName);
            AddParameter("aUser", aUser);
            AddParameter("aTable", aTable);
            AddParameter("DB", DB);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        

        public Boolean IsExist_aFilter(
            Int32 TID
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);

            AddParameter("TID", TID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(aFilterInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable executeSelectQuery(string sqlString, ref string sErr)
        {
            string query = sqlString;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

    }
}
